// 17.图书管理系统 + 数据库版
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class daily_practice_17 {
    public static void main(String[] args) {
        System.out.println("欢迎使用图书管理系统");
        System.out.println("请输入您的选择：");
        Scanner scanner = new Scanner(System.in);
        String choose, title, author, summary, note;
        int price, stock;
        label: // 标签
        while(true){
            System.out.println("1. 添加图书");
            System.out.println("2. 删除图书");
            System.out.println("3. 修改图书");
            System.out.println("4. 查询图书");
            System.out.println("5. 退出系统");
            choose = scanner.next();
            switch (choose) {
                case "1": // 添加图书
                    System.out.println("请输入添加的书本数量：");
                    try {
                        List<Book> book_list = new ArrayList<>();
                        int num = scanner.nextInt();
                        while (num-- > 0) {
                            Book book = new Book();
                            System.out.print("请输入书名：");
                            title = scanner.next();
                            System.out.print("请输入作者：");
                            author = scanner.next();
                            System.out.print("请输入总结：");
                            summary = scanner.next();
                            System.out.print("请输入价格：");
                            price = scanner.nextInt();
                            System.out.print("请输入库存：");
                            stock = scanner.nextInt();
                            System.out.print("请输入备注：");
                            note = scanner.next();
                            if(book.addBook(title, author, summary, price, stock, note, null)){
                                book_list.add(book);
                                System.out.printf("%s 就绪！%n", title);
                            } else {
                                System.out.printf("%s 失败！%n", title);
                            }
                        }
                        MySql mySql = new MySql();
                        if(mySql.add(book_list)){
                            System.out.println("数据库添加成功！");
                        } else {
                            System.out.println("数据库添加失败！");
                        }
                    } catch (Exception e) {
                        System.out.println("输入有误，请重新输入！" + e.getMessage());
                    }
                    break;
                case "2": // 删除图书
                    System.out.println("请输入要删除的书本数量：");
                    try {
                        int num = scanner.nextInt();
                        List<String> title_list = new ArrayList<>();
                        for(int i = 0; i < num; i++){
                            System.out.print("请输入要删除的书名：");
                            title_list.add(scanner.next());
                        }
                        MySql mySql = new MySql();
                        if(mySql.delete(title_list)){
                            System.out.println("数据库删除成功！");
                        } else {
                            System.out.println("数据库删除失败！");
                        }
                    } catch (Exception e) {
                        System.out.println("输入有误，请重新输入！" + e.getMessage());
                    }
                    break;
                case "3": // 修改图书
                    System.out.println("请输入要修改的书本数量：");
                    try {
                        List<String> title_list = new ArrayList<>();
                        List<Integer> stock_list = new ArrayList<>();
                        int num = scanner.nextInt();
                        while (num-- > 0) {
                            System.out.print("请输入书名：");
                            title_list.add(scanner.next());
                            System.out.print("请输入库存量：");
                            stock_list.add(scanner.nextInt());
                        }
                        MySql mySql = new MySql();
                        if(mySql.update(title_list, stock_list)){
                            System.out.println("数据库修改成功！");
                        } else {
                            System.out.println("数据库修改失败！");
                        }
                    } catch (Exception e) {
                        System.out.println("输入有误，请重新输入！" + e.getMessage());
                    }
                    break;
                case "4": // 查询图书
                    System.out.println("请输入要查询的书本数量：");
                    try{
                        int num = scanner.nextInt();
                        List<String> title_list = new ArrayList<>();
                        for(int i = 0; i < num; i++){
                            System.out.print("请输入要查询的书名：");
                            title_list.add(scanner.next());
                        }
                        MySql mySql = new MySql();
                        List<Book> books = mySql.query(title_list);
                        for(Book book : books){ // 遍历集合
                            System.out.printf("书本名：%s, 作者：%s, 总结：%s, 价格：%s, 存量：%s, 备注：%s, 更新时间：%s\n", book.title, book.author, book.summary, book.price, book.stock, book.note, book.time);
                        }
                    } catch (Exception e){
                        System.out.println("输入有误，请重新输入！" + e.getMessage());
                    }
                    break;
                case "5": // 退出系统
                    System.out.println("您选择了退出系统！");
                    break label; // 跳出循环
                default: // 其他情况
                    System.out.println("输入有误，请重新输入！");
                    break;
            }
        }
    }

    // 图书类
    public static class Book{
        private String title; // 书名
        private String author; // 作者
        private String summary; // 简介
        private int price; // 价格
        private int stock; // 库存
        private String note; // 备注
        private Timestamp time; // 更新的时间

        // 构造函数
        public Book(){
        }

        // 添加图书
        public boolean addBook(String title, String author, String summary, int price, int stock, String note, Timestamp time){
            boolean flag = true;
            try {
                this.title = title;
                this.author = author;
                this.summary = summary;
                this.price = price;
                this.stock = stock;
                this.note = note;
                this.time = time;
            } catch (Exception e) {
                // throw new RuntimeException(e);
                System.out.println("添加图书失败！" + e.getMessage()); // 打印错误信息
                flag = false;
            }
            return flag;
        }

    }

    // 数据库类
    public static class MySql{
        private String _url = "jdbc:mysql://localhost:3306/java_project"; // 数据库连接地址
        private String _username = "root"; // 数据库用户名
        private String _password = "3014007467"; // 数据库密码
        // private Connection conn = null; // 数据库连接对象
        private Statement _sta = null; // 连接对象

        // 构造函数
        public MySql(){
            try{
                // Class.forName("com.mysql.jdbc.Driver"); // 加载驱动
                Connection conn = DriverManager.getConnection(this._url, this._username, this._password); // 连接数据库
                this._sta = conn.createStatement();
            }catch(Exception e){
                // e.printStackTrace();
                System.out.println("连接失败！");
            }
        }

        // 修改配置
        public boolean setting(){
            System.out.println("您正在修改配置！");
            boolean flag = true;
            try(Scanner scanner = new Scanner(System.in)) {
                System.out.print("请输入新的数据库连接地:");
                this._url = scanner.next();
                System.out.print("请输入新的数据库用户名:");
                this._username = scanner.next();
                System.out.print("请输入新的数据库密码:");
                this._password = scanner.next();
            } catch (Exception e) {
                System.out.println("输入有误，请重新输入！" + e.getMessage());
                flag = false;
            }
            return flag;
        }

        // 添加图书
        public boolean add(List<Book> book_list){
            boolean flag = true;
            StringBuilder data_list = new StringBuilder();
            for(Book book: book_list){ // 遍历图书列表
                data_list.append(String.format("('%s', '%s', '%s', %s, %s, '%s'),", book.title, book.author, book.summary, book.price, book.stock, book.note));
            }
            if(!data_list.isEmpty()){ // 判断是否为空
                data_list.setLength(data_list.length() - 1);
            }
            String sql = String.format("INSERT INTO booktable (title, author, summary, price, stock, note) VALUES %s;", data_list);
            try {
                // System.out.println(sql);
                this._sta.execute(sql);
                System.out.println("执行SQL成功！");
            } catch (SQLException e) {
                System.out.println("执行SQL时出现错误：" + e.getMessage());
                flag = false;
            }
            return flag;
        }

        // 删除图书
        public boolean delete(List<String> book_list){
            boolean flag = true;
            StringBuilder data_list = new StringBuilder();
            for(String title: book_list){
                data_list.append(String.format("'%s',", title));
            }
            if(!data_list.isEmpty()){
                data_list.setLength(data_list.length() - 1);
            }
            String sql = String.format("DELETE FROM booktable WHERE title IN (%s);", data_list);
            try {
                this._sta.execute(sql);
                System.out.println("执行SQL成功！");
            } catch (SQLException e) {
                System.out.println("执行SQL时出现错误：" + e.getMessage());
                flag = false;
            }
            return flag;
        }

        // 修改图书
        public boolean update(List<String> title_list, List<Integer> stock_list){
            boolean flag = true;
            for(int i = 0; i < title_list.size(); i++){
                String sql = String.format("UPDATE booktable SET stock = %s WHERE title = '%s';", stock_list.get(i), title_list.get(i));
                try {
                    this._sta.execute(sql);
                    System.out.println("执行SQL成功！");
                } catch (SQLException e) {
                    System.out.println("执行SQL时出现错误：" + e.getMessage());
                    flag = false;
                }
            }
            return flag;
        }

        // 查询图书
        public List<Book> query(List<String> list){
            List<Book> book_list = new ArrayList<>();
            if (list.isEmpty()){ // 判断是否为空
                try {
                    ResultSet resultSet = this._sta.executeQuery("SELECT id, title, author, summary, price, stock, note, updated_at FROM booktable;");
                    while (resultSet.next()) {
                        Book book = new Book();
                        book.title = resultSet.getString("title");
                        book.author = resultSet.getString("author");
                        book.summary = resultSet.getString("summary");
                        book.price = resultSet.getInt("price");
                        book.stock = resultSet.getInt("stock");
                        book.note = resultSet.getString("note");
                        book.time = resultSet.getTimestamp("updated_at");
                        book_list.add(book);
                    }
                } catch (SQLException e) {
                    System.out.println("执行SQL时出现错误：" + e.getMessage());
                }
            } else {
                StringBuilder data_list = new StringBuilder();
                for(String title: list){
                    data_list.append(String.format("'%s',", title));
                }
                if(!data_list.isEmpty()){
                    data_list.setLength(data_list.length() - 1);
                }
                String sql = String.format("SELECT id, title, author, summary, price, stock, note, updated_at FROM booktable WHERE title IN (%s);", data_list);
                try {
                    ResultSet resultSet = this._sta.executeQuery(sql);
                    while (resultSet.next()) {
                        Book book = new Book();
                        book.title = resultSet.getString("title");
                        book.author = resultSet.getString("author");
                        book.summary = resultSet.getString("summary");
                        book.price = resultSet.getInt("price");
                        book.stock = resultSet.getInt("stock");
                        book.note = resultSet.getString("note");
                        book.time = resultSet.getTimestamp("updated_at");
                        book_list.add(book);
                    }
                } catch (SQLException e){
                    System.out.println("执行SQL时出现错误：" + e.getMessage());
                }
            }
            return book_list;
        }
    }
}
